package com.award.jdbc.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import com.award.jdbc.connection.JdbcConnection;
import com.award.model.Advertising;
import com.award.model.Commodity;
import com.award.model.Manager;
import com.award.model.Relationship;
import com.award.model.User;
import com.award.model.Winner;

public class JdbcDao {
	private Connection conn = null;
	private PreparedStatement ptmt = null;
	private ResultSet rs = null;

	public User queryEmployee(String username, String password)
			throws Exception {

		JdbcConnection jdbcConnection = new JdbcConnection();
		conn = jdbcConnection.getConn();

		User e = null;
		String sb = "select * from userInformation where username =  ? and password=?";
		ptmt = conn.prepareStatement(sb);
		ptmt.setString(1, username);
		ptmt.setString(2, password);
		rs = ptmt.executeQuery();
		if (rs.next()) {
			e = new User();
			e.setUsername(rs.getString("username"));
			e.setName(rs.getString("name"));
			e.setPassword(rs.getString("password"));
			e.setId(rs.getInt("id"));
			e.setUserImgURL(rs.getString("userImageURL"));
			e.setAddress(rs.getString("address"));
			e.setTelephone(rs.getString("telephone"));
		}
		this.close();

		return e;
	}

	public Manager queryManager(String username, String password)
			throws Exception {

		JdbcConnection jdbcConnection = new JdbcConnection();
		conn = jdbcConnection.getConn();
		Manager e = null;
		String sb = "select * from manager where username =  ? and password=?";
		ptmt = conn.prepareStatement(sb);
		ptmt.setString(1, username);
		ptmt.setString(2, password);
		rs = ptmt.executeQuery();
		if (rs.next()) {
			e = new Manager();
			e.setUsername(rs.getString("username"));
			e.setPassword(rs.getString("password"));
		}
		this.close();
		return e;
	}

	public Relationship queryRelationshipByOne(int userID, int commodityID)
			throws Exception {

		JdbcConnection jdbcConnection = new JdbcConnection();
		conn = jdbcConnection.getConn();
		Relationship r = null;
		String sb = "select * from relationship where userID =  ? and commodityID=?";
		ptmt = conn.prepareStatement(sb);
		ptmt.setInt(1, userID);
		ptmt.setInt(2, commodityID);

		rs = ptmt.executeQuery();
		if (rs.next()) {
			r = new Relationship();
			r.setChecked(rs.getInt("checked"));
			r.setUserID(rs.getInt("userID"));
			r.setCommodityID(rs.getInt("commodityID"));
			r.setSelectNum(rs.getInt("selectNum"));
		}
		this.close();
		return r;
	}

	public Relationship queryRelationshipByLuckyNum(int selectNum,
			int commodityID) throws Exception {
		JdbcConnection jdbcConnection = new JdbcConnection();
		conn = jdbcConnection.getConn();
		Relationship r = null;
		String sb = "select * from relationship where selectNum =  ? and commodityID=?";
		ptmt = conn.prepareStatement(sb);
		ptmt.setInt(1, selectNum);
		ptmt.setInt(2, commodityID);
		rs = ptmt.executeQuery();
		if (rs.next()) {
			r = new Relationship();
			r.setChecked(rs.getInt("checked"));
			r.setUserID(rs.getInt("userID"));
			r.setCommodityID(rs.getInt("commodityID"));
			r.setSelectNum(rs.getInt("selectNum"));
		}
		this.close();
		return r;
	}

	public User queryEmployeeByID(int userID) throws Exception {

		JdbcConnection jdbcConnection = new JdbcConnection();
		conn = jdbcConnection.getConn();
		User e = null;
		String sb = "select * from userInformation where id=?";
		ptmt = conn.prepareStatement(sb);
		ptmt.setInt(1, userID);
		rs = ptmt.executeQuery();
		if (rs.next()) {
			e = new User();
			e.setUsername(rs.getString("username"));
			e.setName(rs.getString("name"));
			e.setPassword(rs.getString("password"));
			e.setId(rs.getInt("id"));
			e.setUserImgURL(rs.getString("userImageURL"));
			e.setAddress(rs.getString("address"));
			e.setTelephone(rs.getString("telephone"));
		}
		this.close();
		return e;
	}

	public Commodity queryCommodityByID(int CommodityID) throws Exception {

		JdbcConnection jdbcConnection = new JdbcConnection();
		conn = jdbcConnection.getConn();
		Commodity commodity = null;
		String sb = "select * from Commodity where id=?";
		ptmt = conn.prepareStatement(sb);
		ptmt.setInt(1, CommodityID);
		rs = ptmt.executeQuery();
		if (rs.next()) {
			commodity = new Commodity();
			commodity.setCommodityName(rs.getString("commodityName"));
			commodity.setId(rs.getInt("id"));
			commodity.setImgURL(rs.getString("imgURL"));
			commodity.setPrice(rs.getFloat("price"));
			commodity.setSelectPeopleNum(rs.getInt("selectPeopleNum"));
			commodity.setAward(rs.getInt("award"));
			commodity.setAwardUserID(rs.getInt("awardUserID"));
			commodity.setTotal(rs.getInt("totalNum"));
		}
		this.close();
		return commodity;
	}

	public List<Commodity> queryAllCommodity() throws Exception {
		List<Commodity> result = new ArrayList<Commodity>();

		JdbcConnection jdbcConnection = new JdbcConnection();
		conn = jdbcConnection.getConn();
		StringBuilder sb = new StringBuilder();
		sb.append("select * from Commodity order by openTime desc;");

		ptmt = conn.prepareStatement(sb.toString());
		java.util.Date date = null;
		SimpleDateFormat bartDateFormat = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm");
		String d = null;
		rs = ptmt.executeQuery();
		Commodity cd = null;
		while (rs.next()) {
			cd = new Commodity();
			cd.setCommodityName(rs.getString("commodityName"));
			cd.setId(rs.getInt("id"));
			cd.setImgURL(rs.getString("imgURL"));
			cd.setPrice(rs.getFloat("price"));
			cd.setSelectPeopleNum(rs.getInt("selectPeopleNum"));
			cd.setAward(rs.getInt("award"));
			d = rs.getString("openTime");
			date = bartDateFormat.parse(d);
			cd.setOpenTime(date);
			cd.setTotal(rs.getInt("totalNum"));
			result.add(cd);
		}
		this.close();
		return result;
	}

	public List<Advertising> queryAllAdvertising() throws Exception {
		List<Advertising> result = new ArrayList<Advertising>();

		JdbcConnection jdbcConnection = new JdbcConnection();
		conn = jdbcConnection.getConn();
		String sb = "select * from advertising;";
		ptmt = conn.prepareStatement(sb);
		rs = ptmt.executeQuery();
		Advertising ad = null;
		while (rs.next()) {
			ad = new Advertising();
			ad.setId(rs.getInt("id"));
			ad.setImgUrl(rs.getString("imgUrl"));

			result.add(ad);
		}
		this.close();
		return result;
	}

	public List<Relationship> queryAllRelationship(int commodityID)
			throws Exception {
		List<Relationship> result = new ArrayList<Relationship>();

		JdbcConnection jdbcConnection = new JdbcConnection();
		conn = jdbcConnection.getConn();
		String sb = "select * from relationship where commodityID = ?";
		ptmt = conn.prepareStatement(sb);
		ptmt.setInt(1, commodityID);
		rs = ptmt.executeQuery();
		Relationship relationship = null;
		while (rs.next()) {
			relationship = new Relationship();
			relationship.setChecked(rs.getInt("checked"));
			relationship.setCommodityID(rs.getInt("commodityID"));
			relationship.setSelectNum(rs.getInt("selectNum"));
			relationship.setUserID(rs.getInt("userID"));

			result.add(relationship);
		}
		this.close();
		return result;
	}

	public List<Relationship> queryOnesRelationship(int userID)
			throws Exception {
		List<Relationship> result = new ArrayList<Relationship>();

		JdbcConnection jdbcConnection = new JdbcConnection();
		conn = jdbcConnection.getConn();
		String sb = "select * from relationship where userID = ?";
		ptmt = conn.prepareStatement(sb);
		ptmt.setInt(1, userID);
		rs = ptmt.executeQuery();
		Relationship relationship = null;
		while (rs.next()) {
			relationship = new Relationship();
			relationship.setChecked(rs.getInt("checked"));
			relationship.setCommodityID(rs.getInt("commodityID"));
			relationship.setSelectNum(rs.getInt("selectNum"));
			relationship.setUserID(rs.getInt("userID"));

			result.add(relationship);
		}
		this.close();
		return result;
	}

	public void insertRelationship(Relationship relationship) throws Exception {
		JdbcConnection jdbcConnection = new JdbcConnection();
		conn = jdbcConnection.getConn();
		String sql = "" + "insert into relationship"
				+ "(userID,commodityID,selectNum,checked)" + "values("
				+ "?,?,?,?)";
		ptmt = conn.prepareStatement(sql);
		ptmt.setInt(1, relationship.getUserID());
		ptmt.setInt(2, relationship.getCommodityID());
		ptmt.setInt(3, relationship.getSelectNum());
		ptmt.setInt(4, relationship.getChecked());
		ptmt.execute();
		this.close();
	}

	public void insertAdvertising(Advertising ad) throws Exception {
		JdbcConnection jdbcConnection = new JdbcConnection();
		conn = jdbcConnection.getConn();
		String sql = "" + "insert into advertising" + "(imgUrl)" + "values("
				+ "?)";
		ptmt = conn.prepareStatement(sql);
		ptmt.setString(1, ad.getImgUrl());
		ptmt.execute();
		this.close();
	}

	public void openTimeComming(int commodityID) throws Exception {
		JdbcConnection jdbcConnection = new JdbcConnection();
		conn = jdbcConnection.getConn();
		String sql = "" + " update Commodity " + " set award=?"
				+ " where id=? ";
		ptmt = conn.prepareStatement(sql);
		ptmt.setInt(1, 0);
		ptmt.setInt(2, commodityID);
		ptmt.execute();
		this.close();
	}

	public void addCommodityNum(int commodityID) throws Exception {
		int updateNum = this.queryCommodityByID(commodityID)
				.getSelectPeopleNum() + 1;
		JdbcConnection jdbcConnection = new JdbcConnection();
		conn = jdbcConnection.getConn();
		String sql = "" + " update Commodity " + " set selectPeopleNum=?"
				+ " where id=? ";
		ptmt = conn.prepareStatement(sql);
		ptmt.setInt(1, updateNum);
		ptmt.setInt(2, commodityID);
		ptmt.execute();
		this.close();
	}

	public void minusCommodityNum(int commodityID) throws Exception {
		int updateNum = this.queryCommodityByID(commodityID)
				.getSelectPeopleNum() - 1;
		JdbcConnection jdbcConnection = new JdbcConnection();
		conn = jdbcConnection.getConn();
		String sql = "" + " update Commodity " + " set selectPeopleNum=?"
				+ " where id=? ";
		ptmt = conn.prepareStatement(sql);
		ptmt.setInt(1, updateNum);
		ptmt.setInt(2, commodityID);

		ptmt.execute();
		this.close();
	}

	public void openAward(int commodityID, int awardUserID) throws SQLException {
		JdbcConnection jdbcConnection = new JdbcConnection();
		conn = jdbcConnection.getConn();
		String sql = "" + " update Commodity " + " set award=?,awardUserID=?"
				+ " where id=? ";
		ptmt = conn.prepareStatement(sql);
		ptmt.setInt(1, 1);
		ptmt.setInt(2, awardUserID);
		ptmt.setInt(3, commodityID);
		ptmt.execute();
		this.close();
	}

	public void oneReffle(int userID, int commodityID) throws SQLException {
		JdbcConnection jdbcConnection = new JdbcConnection();
		conn = jdbcConnection.getConn();
		String sql = "" + " update relationship " + " set checked=?"
				+ " where userID=? and commodityID=? ";
		ptmt = conn.prepareStatement(sql);
		ptmt.setInt(1, 1);
		ptmt.setInt(2, userID);
		ptmt.setInt(3, commodityID);
		ptmt.execute();
		this.close();
	}

	public void insertWinner(Winner winner) throws Exception {
		JdbcConnection jdbcConnection = new JdbcConnection();
		conn = jdbcConnection.getConn();
		SimpleDateFormat bartDateFormat = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm");
		String sql = ""
				+ "insert into winner"
				+ "(winTime,commodityID,winnerID,winnerName,commodityName,commodityPrice)"
				+ "values(" + "?,?,?,?,?,?)";
		ptmt = conn.prepareStatement(sql);
		Date d = new Date(winner.getWinTime().getTime());
		ptmt.setString(1, bartDateFormat.format(d));
		ptmt.setInt(2, winner.getCommodityID());
		ptmt.setInt(3, winner.getWinnerID());
		ptmt.setString(4, winner.getWinnerName());
		ptmt.setString(5, winner.getCommodityName());
		ptmt.setFloat(6, winner.getCommodityPrice());
		ptmt.execute();
		this.close();
	}

	public List<Winner> queryAllWinner() throws Exception {
		List<Winner> result = new ArrayList<Winner>();

		JdbcConnection jdbcConnection = new JdbcConnection();
		conn = jdbcConnection.getConn();
		StringBuilder sb = new StringBuilder();
		sb.append("select * from winner order by winTime desc");
		ptmt = conn.prepareStatement(sb.toString());
		rs = ptmt.executeQuery();
		java.util.Date date = null;
		SimpleDateFormat bartDateFormat = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm");
		String d = null;
		Winner winner = null;
		while (rs.next()) {
			winner = new Winner();
			d = rs.getString("winTime");
			date = bartDateFormat.parse(d);
			winner.setWinTime(date);
			winner.setCommodityID(rs.getInt("commodityID"));
			winner.setWinnerID(rs.getInt("winnerID"));
			winner.setWinnerName(rs.getString("winnerName"));
			winner.setCommodityName(rs.getString("commodityName"));
			winner.setCommodityPrice(rs.getFloat("commodityPrice"));

			result.add(winner);
		}
		this.close();
		return result;
	}

	public void deleteRelationship(int userID, int commodityID)
			throws Exception {
		JdbcConnection jdbcConnection = new JdbcConnection();
		conn = jdbcConnection.getConn();
		String sql = "" + " delete from relationship "
				+ " where userID=? and commodityID=?";
		ptmt = conn.prepareStatement(sql);
		ptmt.setInt(1, userID);
		ptmt.setInt(2, commodityID);
		ptmt.execute();
		this.close();
	}

	public void deleteAdvertising(int id) throws Exception {
		JdbcConnection jdbcConnection = new JdbcConnection();
		conn = jdbcConnection.getConn();
		String sql = "" + " delete from advertising " + " where id=?";
		ptmt = conn.prepareStatement(sql);
		ptmt.setInt(1, id);
		ptmt.execute();
		this.close();
	}

	/**
	 * ------------------------------------------------------------------------
	 * --------------------------------------------------
	 * 
	 * @author scc
	 * 
	 */
	public boolean insertUser(User user) throws SQLException {
		JdbcConnection jdbcConnection = new JdbcConnection();
		conn = jdbcConnection.getConn();
		String sql = "insert into userInformation (username, userImageURL, telephone, password, name, wechat_access_token, address) value (?,?,?,?,?,?,?) ";
		ptmt = conn.prepareStatement(sql);
		ptmt.setString(1, (user.getUsername() != null) ? user.getUsername()
				: "");
		ptmt.setString(2, (user.getUserImgURL() != null) ? user.getUserImgURL()
				: "");
		ptmt.setString(3, (user.getTelephone() != null) ? user.getTelephone()
				: "");
		ptmt.setString(4, (user.getPassword() != null) ? user.getPassword()
				: "");
		ptmt.setString(5, (user.getName() != null) ? user.getName() : "");
		ptmt.setString(
				6,
				(user.getWechat_access_token() != null) ? user
						.getWechat_access_token() : "");
		ptmt.setString(7, (user.getAddress() != null) ? user.getAddress() : "");
		int row = ptmt.executeUpdate();
		if (row == 1) {
			this.close();
			return true;
		}
		this.close();
		return false;

	}

	public String getIdByUsername(String username) throws SQLException {
		JdbcConnection jdbcConnection = new JdbcConnection();
		conn = jdbcConnection.getConn();
		String sql = "select id from userInformation where username = ?";
		ptmt = conn.prepareStatement(sql);
		ptmt.setString(1, username);
		rs = ptmt.executeQuery();
		rs.next();
		String id = rs.getString("id");
		this.close();
		return id;
	}

	public String getIdByPhone(String phone) throws SQLException {
		JdbcConnection jdbcConnection = new JdbcConnection();
		conn = jdbcConnection.getConn();
		String sql = "select id from userInformation where telephone = ?";
		ptmt = conn.prepareStatement(sql);
		ptmt.setString(1, phone);
		rs = ptmt.executeQuery();
		rs.next();
		String id = rs.getString("id");
		this.close();
		return id;
	}

	public User getUserById(int id) throws SQLException {
		JdbcConnection jdbcConnection = new JdbcConnection();
		conn = jdbcConnection.getConn();
		String sql = "select * from userInformation where id= ?";
		ptmt = conn.prepareStatement(sql);
		ptmt.setInt(1, id);
		rs = ptmt.executeQuery();
		User user = null;
		if (rs.next()) {
			user = new User();
			user.setUsername(rs.getString("username"));
			user.setName(rs.getString("name"));
			user.setPassword(rs.getString("password"));
			user.setId(rs.getInt("id"));
			user.setUserImgURL(rs.getString("userImageURL"));
		}
		this.close();
		return user;
	}

	public String getPasswordByUsername(String username) throws SQLException {

		JdbcConnection jdbcConnection = new JdbcConnection();
		conn = jdbcConnection.getConn();
		String sql = "select password from userInformation where username= ?";
		ptmt = conn.prepareStatement(sql);
		ptmt.setString(1, username);
		rs = ptmt.executeQuery();
		if (rs.next()) {
			String password = rs.getString("password");
			this.close();
			return password;
		}
		this.close();
		return null;
	}

	public boolean isUsername(String username) throws SQLException {
		JdbcConnection jdbcConnection = new JdbcConnection();
		conn = jdbcConnection.getConn();
		String sql = "select username from userInformation where username= ?";
		ptmt = conn.prepareStatement(sql);
		ptmt.setString(1, username);
		rs = ptmt.executeQuery();
		if (rs.next()) {
			this.close();
			return true;
		}
		this.close();
		return false;
	}

	public boolean isPhone(String phone) throws SQLException {
		JdbcConnection jdbcConnection = new JdbcConnection();
		conn = jdbcConnection.getConn();
		String sql = "select telephone from userInformation where telephone= ?";
		ptmt = conn.prepareStatement(sql);
		ptmt.setString(1, phone);
		rs = ptmt.executeQuery();
		if (rs.next()) {
			this.close();
			return true;
		}
		this.close();
		return false;
	}

	public String getPasswordByPhone(String phone) throws SQLException {

		JdbcConnection jdbcConnection = new JdbcConnection();
		conn = jdbcConnection.getConn();
		String sql = "select password from userInformation where telephone= ?";
		ptmt = conn.prepareStatement(sql);
		ptmt.setString(1, phone);
		rs = ptmt.executeQuery();
		if (rs.next()) {
			String password = rs.getString("password");
			this.close();
			return password;
		}
		this.close();
		return null;
	}

	public void updateUser(User user) throws SQLException {
		int id = user.getId();
		System.out.println("jdbc:" + user.getAddress() + " "
				+ user.getUsername());
		JdbcConnection jdbcConnection = new JdbcConnection();
		conn = jdbcConnection.getConn();
		String sql = "update userInformation set userImageURL= ? , username=?,address= ? ,password=?,name=? where id= ?";
		ptmt = conn.prepareStatement(sql);
		ptmt.setString(1, user.getUserImgURL());
		ptmt.setString(2, user.getUsername());
		ptmt.setString(3, user.getAddress());
		ptmt.setString(4, user.getPassword());
		ptmt.setString(5, user.getName());
		ptmt.setInt(6, id);
		ptmt.executeUpdate();
		this.close();
	}

	public void insertCommodity(Commodity commodity) throws SQLException {
		JdbcConnection jdbcConnection = new JdbcConnection();
		conn = jdbcConnection.getConn();
		String sql = "insert into Commodity (commodityName,price,selectPeopleNum,imgURL,award,awardUserID,openTime,totalNum)"
				+ " values (?,?,?,?,?,?,?,?)";
		ptmt = conn.prepareStatement(sql);
		ptmt.setString(1, commodity.getCommodityName());
		ptmt.setFloat(2, commodity.getPrice());
		ptmt.setInt(3, commodity.getSelectPeopleNum());
		ptmt.setString(4, commodity.getImgURL());
		ptmt.setInt(5, commodity.getAward());
		ptmt.setInt(6, commodity.getAwardUserID());
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String openTime = df.format(commodity.getOpenTime());
		ptmt.setString(7, openTime);
		ptmt.setInt(8, commodity.getTotal());
		ptmt.execute();
		this.close();
	}

	public void close() {
		try {
			if (rs != null) {
				rs.close();
			}
			if (ptmt != null) {
				ptmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}
}
